package com.eportal.service.impl;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpSession;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.struts2.ServletActionContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eportal.aop.LogAnnotation;
import com.eportal.base.BaseDAO;
import com.eportal.domain.CBuilding;
import com.eportal.domain.CInstallUnit;
import com.eportal.domain.CRoom;
import com.eportal.domain.RoomUser;
import com.eportal.domain.Sysopt;
import com.eportal.domain.TCommunity;
import com.eportal.domain.TKeys;
import com.eportal.domain.TUsers;
import com.eportal.domain.User;
import com.eportal.domain.UsersCommunity;
import com.eportal.exception.MdKnownRunTimeException;
import com.eportal.service.CInstallUnitService;
import com.eportal.service.CRoomService;
import com.eportal.service.TCommunityService;
import com.eportal.service.TKeysService;
import com.eportal.service.TUsersService;
import com.eportal.service.UsersCommunityService;
import com.eportal.util.ValidateUtil;
import com.framework.excelTools.ExcelException;
import com.framework.jqgrid.JqGridPager;

/**
 * 楼栋管理业务逻辑接口实现
 */
@Service("cRoomService")
public class CRoomServiceImpl implements CRoomService {

	private Map<String, Object> result = new HashMap<String, Object>();
	private static Logger log = Logger.getLogger(CInstallUnitServiceImpl.class.getName());
	@Resource(name="dao")
	private BaseDAO dao;
	@Autowired
    private TUsersService tUsersService;
    @Autowired
    private TCommunityService tCommunityService;
    @Autowired
    private TKeysService tKeysService;
	@Override
    public List<CRoom> list(String where) {
    	List<CRoom> list= null;
    	if (where == null) {
    		list = dao.query(" from CRoom");
		}else{
			list = dao.query(" from CRoom "+where);
		}
    	return list;
    }

	@Override
	public Map<String, String> getBySql(Long sid) {
		List<Map<String, String>> list= null;
		StringBuilder sql = new StringBuilder("select b.*,t02_agtent.agtname from t10_appstore b left join t02_agtent on b.agtcode=t02_agtent.agtcode  where b.sid=");
		sql.append(sid);
		list= dao.queryBySql(sql.toString());
		if (list.size()>0) {
			return list.get(0);
		}
		return null;
	}
	
	
	@Override
	public List<Map<String, Object>> getRoomByUnit(String unitId) {
		StringBuffer sql = new StringBuffer("select t.sid,t.room_no as roomno,t.floor_no as floorno from T035_ROOM t where t.unit_id ='");
		sql.append(unitId);
		sql.append("'");
		List<Map<String, Object>> listMap =  dao.queryBySql(sql.toString());
		return listMap;
	}
	
	
    @Override
    public CRoom add(CRoom cRoom) throws Exception {
    	dao.save(cRoom);
        return cRoom;
    }

    @Override
    public void delete(Long id) {
    	CRoom cRoom = (CRoom) dao.getById(CRoom.class, id);
    	dao.delete(cRoom);
    	dao.delByConditions("RoomUser", "roomId='"+String.valueOf(id)+"'");
    }

    @Override
    public void delete(List<Long> ids) {
        for (Long id : ids) {
            delete(id);
        }
    }
    
    public CRoom get(Long sid) {
        return (CRoom) dao.getById(CRoom.class, sid);
    }
    
    public Map<String, String> getRoom(Long sid) {
    	List<Map<String, String>> list= null;
		//StringBuilder sql = new StringBuilder("select t.*,u.sid as unitSid,b.sid as buidingSid from T035_ROOM t left join t032_cinstallunit u on t.unit_id=u.sid left join t031_cbuilding b on u.cbuildingid=b.sid where t.sid =");
    	StringBuilder sql = new StringBuilder("select t.*,u.sid as unitSid,u.cbuildingid as buidingSid from T035_ROOM t left join t032_cinstallunit u on t.unit_id=u.sid  where t.sid =");
		sql.append(sid);
		list= dao.queryBySql(sql.toString());
		if (list.size()>0) {
			return list.get(0);
		}
		return null;
    }

    @Override
    public void update(CRoom cRoom) throws Exception {
        
        dao.update(cRoom);
    }
    
    public void updateObject(Object entity) throws Exception {
    	dao.update(entity);
	}

	 /**
     * 房间修改保存方法
     */
	@Override
    @LogAnnotation(operateDescribe="房间修改")  
    public Map<String, Object> updateMethod(CRoom cRoom) {
		try {
	        String msg = ValidateUtil.validate(cRoom);
	        result.put("objectId", cRoom.getSid());
	        if (msg == null || msg.trim().length() == 0) {
	            	CRoom entity = get(cRoom.getSid());
	            	List<Map<String, Object>> list = getRoom(entity.getUnitId(),cRoom.getRoomNo());
	            	if(list.size()>0 && !entity.getRoomNo().equals(cRoom.getRoomNo())){//存在，但是不是自己的。不能提供修改
	            		result.put("code", 0);
				         result.put("msg", "该单元下房间名称已存在，请修改后保存");
				         return result;
	            	}
	            	entity.setUnitId(cRoom.getUnitId());
	            	entity.setFloorNo(cRoom.getFloorNo());
	            	entity.setRoomNo(cRoom.getRoomNo());
	            	entity.setOwnerName(cRoom.getOwnerName());
	            	
	            	entity.setOwnerIdNumber(cRoom.getOwnerIdNumber());
	            	entity.setStatus(cRoom.getStatus());
	            	entity.setRenterName(cRoom.getRenterName());
	            	
	            	entity.setRenterIdNumber(cRoom.getRenterIdNumber());
	            	Sysopt  user= (Sysopt)getSession().getAttribute("user");
	            	entity.setuUserName(Long.toString(user.getSid()));
	            	entity.setuTime(new Timestamp(System.currentTimeMillis()));
//	            	//手机号改变
//	            	if (!cRoom.getOwnerTel().equals(entity.getOwnerTel())) {
	            		//查询用户手机号，更新到用户关系表
	            		TUsers Owner = tUsersService.getOrCreatTuser(entity.getCommunityId(),cRoom.getOwnerTel(),cRoom.getOwnerName(),entity.getUnitId());
	            		entity.setOwnerId(Owner.getUserId());
//					}
	            	entity.setOwnerTel(cRoom.getOwnerTel());
	    			if ("2".equals(cRoom.getStatus())) {
	    				//查询用户手机号，更新到用户关系表
	    				TUsers renter = tUsersService.getOrCreatTuser(entity.getCommunityId(),cRoom.getRenterTel(),cRoom.getRenterName(),entity.getUnitId());
	    				entity.setRenterId(renter.getUserId());
	    				saveOrUpdateRoomUser(String.valueOf(entity.getSid()), renter, "2");
	    			}
	    			entity.setRenterTel(cRoom.getRenterTel());
	            	update(entity);
	    			saveOrUpdateRoomUser(String.valueOf(entity.getSid()), Owner, "1");
	                result.put("code", 1);
	                result.put("msg", "操作成功");
	        } else {
	            result.put("code", 0);
	            result.put("msg", msg);
	        }
		} catch (Exception e) {
			log.error("安装点更新失败", e);
			result.put("code", 0);
			result.put("msg", "操作失败");
		}
        return result;
    }
	
	public Object getObjectHql(String name,String where) {
		StringBuffer sb = new StringBuffer("from ");
		sb.append(name);
		sb.append(" where ");
		sb.append(where);
	     return  dao.loadObject(sb.toString());
	}
	

	@Override
	/**
     * 房间删除方法,主要用于切面记录操作日志
     */
    @LogAnnotation(operateDescribe="房间删除")  
    public Map<String, Object> deleteMethod(String param_ids,String communityid) throws ExcelException {
        try {
            result.put("objectId", param_ids);
            for (String s : param_ids.split(",")) {
            	CRoom room = (CRoom)dao.getById(CRoom.class, Long.valueOf(s));
            	if(room!=null){
            		String unitid = room.getUnitId();
                	dao.delById(CRoom.class,Long.valueOf(s));
            		int i = dao.countQuery("select count(*) from CRoom where unitId='"+unitid+"'");
            		if(i==0){
            			CInstallUnit unit = (CInstallUnit)dao.getById(CInstallUnit.class, Long.valueOf(unitid));
            			if(unit!=null){
            				String bid = unit.getcBuildingId();
            				dao.delById(CInstallUnit.class,unit.getSid());
            				int j = dao.countQuery("select count(*) from CInstallUnit where cBuildingId='"+bid+"'");
            				if(j==0){
            					dao.delById(CBuilding.class,Long.valueOf( bid));
            				}
            				
            			}
            			
            		}
            	}
            }
            result.put("msg", "操作成功");
            result.put("code", 1);
        } catch (Exception e) {
            log.error("房间删除失败", e);
            result.put("code", 0);
            result.put("msg", "操作失败");
            throw new ExcelException(e);
        }
        return result;
    }
	
	@Override
	/**
     * 房间住户删除方法,主要用于切面记录操作日志
     */
    @LogAnnotation(operateDescribe="房间住户删除")  
    public Map<String, Object> deleteUser(String sid) {
        try {
        	//删除房间住户表数据
        	RoomUser roomUser = (RoomUser) dao.getById(RoomUser.class, Long.valueOf(sid));
        	dao.delete(roomUser);
        	//将用户社区关系表中该用户在当前房间所在社区的数据状态改为禁用：‘3’
        	Long roomid = Long.valueOf(roomUser.getRoomId());
        	CRoom cRoom = (CRoom) dao.getById(CRoom.class, roomid);
        	String communityId = cRoom.getCommunityId();
        	StringBuilder sb = new StringBuilder();
        	sb.append("update UsersCommunity  set status='3' where communityId='");
        	sb.append(communityId);
        	sb.append("' and userId='");
        	sb.append(roomUser.getUserId());
        	sb.append("'");
        	dao.update(sb.toString());
        	//停用该用户在当前社区的钥匙
        	sb.delete(0, sb.length());
        	sb.append("update TKeys  set status='1' where communityId='");
        	sb.append(communityId);
        	sb.append("' and userId='");
        	sb.append(roomUser.getUserId());
        	sb.append("'");
        	dao.update(sb.toString());
            result.put("msg", "操作成功");
            result.put("code", 1);
        } catch (Exception e) {
            log.error("住户删除失败", e);
            result.put("code", 0);
            result.put("msg", "操作失败");
        }
        return result;
    }
	
	public HttpSession getSession() {
        return ServletActionContext.getRequest().getSession();
    }

	@Override
	public List<CRoom> list(JqGridPager jqGridPager) {
		String sql = "select t.*,b.cbname||u.cbname||t.ROOM_NO as roomNo from T035_ROOM t left join t032_cinstallunit u on t.unit_id=u.sid left join t031_cbuilding b on u.cbuildingid=b.sid ";
		return dao.query(sql, jqGridPager);
	}

	@Override
	public List<RoomUser> listUser(JqGridPager jqGridPager) {
		String sql = "select t.*,us.auno from t035_room_user t left join t04_users us on t.app_userid = us.userid";
		return dao.query(sql, jqGridPager);
	}

	@Override
	public List<Map<String, String>> listBySql(String hql) {
		return dao.queryBySql(hql);
	}
	
	//房间住户新增
	@Override
	@LogAnnotation(operateDescribe="房间住户新增")  
	public Map<String, Object> addRoomUser(String communityId, String roomId, String userName,
			String Tel, String userIdNumber, String status) {
		// TODO Auto-generated method stub
		try{
		 result = new HashMap<String, Object>();
		 Sysopt  user= (Sysopt)getSession().getAttribute("user");
		 TCommunity tCommunity =  tCommunityService.get(Long.parseLong(communityId));
		 String appId = tCommunity.getAppId();
		 String userId = "";
		 String queryhql = "select * from t04_users where appid = '"+appId+"' and tel = '"+Tel+"'";
		 List<Map<String, String>> tuserList = listBySql(queryhql);
		 if(tuserList.size()!=0){
			 userId = tuserList.get(0).get("userid");
		 }
		 //如果用户不存在 则新增一条用户数据
		 if(tuserList.size()==0){
			 TUsers tuser = new TUsers();
			 tuser.setAppId(appId);
			 tuser.setTel(Tel);
			 tuser.setAuTel(Tel);
			 tuser.setAgtCode(tCommunity.getAgtCode());
			 tuser.setAuName(userName);
			 tuser.setNickName(Tel.substring(Tel.length()-4));
			 if(StringUtils.isNotBlank(userIdNumber)){
				 tuser.setAuNo(userIdNumber);
			 }
			 tuser.setcTime(new Timestamp(System.currentTimeMillis()));
			 tuser.setcUserName(user.getSid().toString());
			 String tuserid = tUsersService.getUserID();
			 tuser.setUserId(tuserid);
			 userId = tuserid;
			 dao.save(tuser);
		 }
		 //插入房间住户信息表
		 RoomUser roomuser = new RoomUser();
		 roomuser.setCTime(new Timestamp(System.currentTimeMillis()));
		 roomuser.setUserId(userId);
		 roomuser.setRoomId(roomId);
		 roomuser.setUserType(status);
		 roomuser.setTel(Tel);
		 roomuser.setUserName(userName);
		 dao.save(roomuser);
         //插入用户社区关系表
         CRoom croom = this.get(Long.valueOf(roomId));
         Long unitId = Long.valueOf(Long.valueOf(croom.getUnitId()));
         tUsersService.updateUsersCommunity(communityId, unitId, userId);
         //给住户分配钥匙
         StringBuilder queryLockHql = new StringBuilder();
         queryLockHql.append("select t.pid,t.install_lock_name lockname from t05_locks t where t.status='1' and t.ispubunit='1' and t.communityid='");
         queryLockHql.append(communityId);
         queryLockHql.append("'");
         queryLockHql.append(" union ");
         queryLockHql.append("select t.pid,t.install_lock_name lockname from t05_locks t where t.status='1' and t.ispubunit='0' and t.communityid='");
         queryLockHql.append(communityId);
         queryLockHql.append("' and t.installunitid='");
         queryLockHql.append(unitId.toString());
         queryLockHql.append("'");
         List<Map<String, String>> lockPidList = listBySql(queryLockHql.toString());
         for(Map<String, String> map : lockPidList){
        	 String Pid = map.get("pid");
        	 String lockname = map.get("lockname");
        	 //TODO  调用此方法有问题，先注释掉 by wangk
        	// updateUserKeys(Pid, lockname, userId, communityId);
         }
         result.put("code", 1);
         result.put("msg", "操作成功");
		}catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			log.error("住户添加失败", e);
			result.put("code", 0);
			result.put("msg", "操作失败");
		}	 
		return result;
	}
	
	@Override
	@LogAnnotation(operateDescribe="房间新增")  
	public Map<String, Object> addMethod(CRoom cRoom) {
		try {
			result = new HashMap<String, Object>();
			Sysopt  user= (Sysopt)getSession().getAttribute("user");
			List<Map<String, Object>> list = getRoom(cRoom.getUnitId(),cRoom.getRoomNo());
			if (list.size()>0) {
				 result.put("code", 0);
		         result.put("msg", "该单元下已存在该房间号，请修改后保存");
		         return result;
			}
			cRoom.setcUserName(Long.toString(user.getSid()));
			cRoom.setcTime(new Timestamp(System.currentTimeMillis()));
			//查询用户手机号，更新到用户关系表
			TUsers Owner = tUsersService.getOrCreatTuser(cRoom.getCommunityId(),cRoom.getOwnerTel(),cRoom.getOwnerName(),cRoom.getUnitId());
			cRoom.setOwnerId(Owner.getUserId());
			TUsers renter = new TUsers();
			if ("2".equals(cRoom.getStatus())) {
				//查询用户手机号，更新到用户关系表
				renter = tUsersService.getOrCreatTuser(cRoom.getCommunityId(),cRoom.getRenterTel(),cRoom.getRenterName(),cRoom.getUnitId());
				cRoom.setRenterId(renter.getUserId());
			}
			cRoom =  add(cRoom);
			if("2".equals(cRoom.getStatus())){
				saveOrUpdateRoomUser(String.valueOf(cRoom.getSid()), renter, "2");
			}
			saveOrUpdateRoomUser(String.valueOf(cRoom.getSid()), Owner, "1");
            result.put("code", 1);
            result.put("msg", "操作成功");
            result.put("objectId", cRoom.getSid());
		} catch (Exception e) {
			log.error("角色保存失败", e);
			result.put("code", 0);
			result.put("msg", "操作失败");
		}
        return result;
	}
	private void saveOrUpdateRoomUser(String roomId, TUsers tuser,String utype)  throws Exception{
		try{
		StringBuilder sb = new StringBuilder();
		sb.append("select * from t035_room_user t where t.room_id='");
		sb.append(roomId);
		sb.append("' and t.utype='");
		sb.append(utype);
		sb.append("'");
		List<Map<String, Object>> list =  dao.queryBySql(sb.toString());
		if(list.size()==0){
			RoomUser roomUser = new RoomUser();
			roomUser.setCTime(new Timestamp(System.currentTimeMillis()));
			roomUser.setUserId(tuser.getUserId());
			roomUser.setRoomId(roomId);
			roomUser.setUserType(utype);
			roomUser.setTel(tuser.getTel());
			roomUser.setUserName(tuser.getAuName());
			dao.save(roomUser);
		}else if(!tuser.getUserId().equals(String.valueOf(list.get(0).get("app_userid")))){
			String sql = "update t035_room_user set app_userid = '"+tuser.getUserId()+"', tel='"+tuser.getAuTel()+
					"', auname='"+tuser.getAuName()+"' where sid="+list.get(0).get("sid");
			dao.updateBySql(sql);
		}
		}catch(Exception e){
			throw new Exception();
		}
	}
	public Map<String, Object> updateUserKeys(String pid, String lockName, String userId, String communityId){
		try {
		StringBuffer sb = new StringBuffer(" pid ='");
		result = new HashMap<String, Object>();
		sb.append(pid);
		sb.append("'");
		sb.append(" and userId='");
		sb.append(userId);
		sb.append("'");
		TKeys key = tKeysService.getByWhere(sb.toString());
		Calendar calendar = Calendar.getInstance();
		calendar.add(Calendar.YEAR, 3);
		if(key == null){
			TKeys newKey = new TKeys();
			newKey.setUserId(userId);
			newKey.setPid(pid);
			newKey.setLockName(lockName);
			newKey.setReqTime(new Timestamp(System.currentTimeMillis()));
			newKey.setValidity(new Timestamp(calendar.getTimeInMillis()));
			newKey.setCommunityId(communityId);
			newKey.setStatus("0");//默认钥匙状态为正常
			tKeysService.add(newKey);
		}else if("1".equals(key.getStatus())){
			key.setStatus("0");
				tKeysService.update(key);
		}
		result.put("code", 1);
		result.put("msg", "操作成功");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			log.error(this, e);
			result.put("code", 0);
            result.put("msg", "操作失败");
            throw new MdKnownRunTimeException("钥匙表更新失败");
		}	
		return result;
	}
	public List<Map<String, Object>> getRoom(String unitId, String roomNo) {
		StringBuilder sql = new StringBuilder("select t.sid,t.room_no as roomno,t.floor_no as floorno from T035_ROOM t where t.unit_id='");
		sql.append(unitId);
		sql.append("' and t.room_no ='");
		sql.append(roomNo);
		sql.append("'");
		return dao.queryBySql(sql.toString());
	}

	@Override
	public List<Map<String, Object>> queryBySql(String unit, String budingid) {
		StringBuffer sql = new StringBuffer();
		
		sql.append("select t.userid,u.tel as usertel,u.auname as username from t04_users_community t  left join t04_users u on t.userid=u.userid where ");
		if (StringUtils.isNotBlank(unit)) {
			sql.append(" t.installunitid in(");
			sql.append(unit);
			sql.append(")");
		}else{
			sql.append(" t.cbuildingId=");
			sql.append(budingid);
		}
		return dao.queryBySql(sql.toString());
	}

	@Override
	public List<Map<String, Object>> listPage(JqGridPager jqGridPager) {
		
		String sql = "select t.*,b.cbname as CBNAME,u.cbname as UNITNAME  from T035_ROOM t left join t032_cinstallunit u on t.unit_id=u.sid left join t031_cbuilding b on u.cbuildingid=b.sid ";
		return dao.query(sql, jqGridPager);
	}



	
}
